require('dotenv').config({ path: '../.env' }); // 指定.env文件路径
const { pool } = require('../config/db');

// 创建家庭相关表的SQL语句
const createFamilyTablesQuery = `
-- 创建家庭表
CREATE TABLE IF NOT EXISTS families (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  address VARCHAR(255),
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建家庭成员关系表
CREATE TABLE IF NOT EXISTS family_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  family_id INT NOT NULL,
  user_id INT NOT NULL,
  role ENUM('admin', 'member') DEFAULT 'member',
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_family_user (family_id, user_id)
);

-- 创建设备共享表
CREATE TABLE IF NOT EXISTS device_sharing (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  family_id INT NOT NULL,
  shared_by INT NOT NULL,
  is_shared BOOLEAN DEFAULT TRUE,
  shared_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE CASCADE,
  FOREIGN KEY (shared_by) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_device_family (device_id, family_id)
);
`;

// 更新用户表，添加关联字段
const updateUserTableQuery = `
ALTER TABLE users
ADD COLUMN default_family_id INT DEFAULT NULL,
ADD FOREIGN KEY (default_family_id) REFERENCES families(id) ON DELETE SET NULL;
`;

async function updateDatabase() {
  let connection;
  try {
    connection = await pool.getConnection();
    console.log('成功连接到数据库。');

    console.log('开始创建家庭相关表...');
    try {
      await connection.query(createFamilyTablesQuery);
      console.log('家庭相关表创建成功：');
      console.log('- families (家庭表)');
      console.log('- family_members (家庭成员关系表)');
      console.log('- device_sharing (设备共享表)');
    } catch (error) {
      console.error('创建家庭相关表失败:', error.message);
      throw error;
    }

    console.log('开始更新用户表，添加家庭关联...');
    try {
      await connection.query(updateUserTableQuery);
      console.log('用户表更新成功，添加了以下字段：');
      console.log('- default_family_id (默认家庭ID，外键关联families表)');
    } catch (error) {
      console.error('更新用户表失败:', error.message);
      // 如果字段已存在，则忽略错误
      if (error.message.includes('Duplicate column name')) {
        console.log('字段已存在，无需添加。');
      } else {
        throw error;
      }
    }

    console.log('数据库更新完成。');

  } catch (error) {
    console.error('数据库更新过程中发生错误:', error);
  } finally {
    if (connection) {
      connection.release();
      console.log('数据库连接已释放。');
    }
    await pool.end(); // 关闭连接池
    console.log('连接池已关闭。');
  }
}

updateDatabase(); 